create or replace package gen_table_package is

procedure gen_code_for_table( ip_table_name varchar2,
                              ip_username varchar2 := 'amazing');

end gen_table_package;
/
create or replace package body gen_table_package is

/*WRITE LINE WITH TAB*/
g_current_number  number := 0;
function get_new_line_number return number is
begin
      g_current_number := g_current_number + 1;
      return g_current_number;
end;

procedure pl is
     v_new_line_number number := get_new_line_number;
begin
  insert into sys_code_generated (stt,code_line) values ( v_new_line_number, '');
end pl;

procedure pl(ip_str varchar2) is
   v_new_line_number number := get_new_line_number;
begin
   insert into sys_code_generated (stt,code_line) values ( v_new_line_number, ip_str);
end;
procedure pl_1tab(ip_str varchar2) is
   v_indent varchar2(10) := chr(9);
   v_new_line_number number := get_new_line_number;
begin
   insert into sys_code_generated (stt,code_line) values ( v_new_line_number, v_indent||ip_str);
end;

procedure pl_2tab(ip_str varchar2) is
 v_indent varchar2(10) := chr(9)||chr(9);
   v_new_line_number number := get_new_line_number;
begin
   insert into sys_code_generated (stt,code_line) values ( v_new_line_number, v_indent||ip_str);
end;
procedure pl_3tab(ip_str varchar2) is
 v_indent varchar2(10) := chr(9)||chr(9)||chr(9);
   v_new_line_number number := get_new_line_number;
begin
   insert into sys_code_generated (stt,code_line) values ( v_new_line_number, v_indent||ip_str);
end;
procedure pl_4tab(ip_str varchar2) is
 v_indent varchar2(10) := chr(9)||chr(9)||chr(9)||chr(9);
   v_new_line_number number := get_new_line_number;
begin
   insert into sys_code_generated (stt,code_line) values ( v_new_line_number, v_indent||ip_str);
end;
/**/
procedure reset_data IS
begin
     delete from sys_code_generated;
end;
/*

*/
function make_input_param(ip_table_name varchar2,
                          ip_column_name varchar2) return varchar2 is
begin
 return 'ip_'||lower(ip_column_name) || ' in ' || ip_table_name||'.'||ip_column_name||'%TYPE';
end make_input_param;

/*make specificatiom*/
procedure gen_speci( ip_table_name varchar2,
                              ip_username varchar2 := 'FMS') is
  cursor v_cur_columns is select column_name, data_type
                          from   user_tab_columns
                          where table_name = ip_table_name;
  v_separator char(1) := ' ';
begin
 pl('create or replace package ' || ip_table_name || '_P is');
 pl('/*');
 pl('|| purpose: basic rutines for table ' || ip_table_name);
 pl('||');
 pl('|| created by: ' || ip_username);
 pl('*/');
 pl;
 pl('/*insert one record into Db, no commit*/');
 pl('procedure insert_'||lower(ip_table_name)||' ( ');

 pl_2tab('op_id out '  ||  ip_table_name || '.ID%TYPE');
 for  v_rec in v_cur_columns loop
         if v_rec.column_name <> 'ID' then
                  pl_2tab(',' ||make_input_param(ip_table_name,v_rec.column_name ));
         end if;
 end loop;
 pl_2tab(');');

 pl('/*delete using ID, no commit*/');
 pl('procedure delete_'||lower(ip_table_name)||' ( ');
 pl_2tab('ip_id ' || ip_table_name ||'.ID%TYPE');
 pl_2tab(');');

 pl('/*update entire record, no commit*/');
 pl('procedure update_'||lower(ip_table_name)||' ( ');
 v_separator := '';
 for  v_rec in v_cur_columns loop
         pl_2tab(v_separator ||make_input_param(ip_table_name,v_rec.column_name ));
         v_separator := ',';
 end loop;
 pl_2tab(');');

 pl('/*END-OF-PACKAGE*/');
 pl('end ' || ip_table_name || '_P;');

end gen_speci;
/*MAKE BODY*/
procedure gen_body( ip_table_name varchar2,
                              ip_username varchar2 := 'FMS') is
  cursor v_cur_columns is select column_name, data_type
                          from   user_tab_columns
                          where table_name = ip_table_name;
  v_separator char(1);
begin
 pl;
 pl;
 pl('/*********************/');

 pl('create or replace package body ' || ip_table_name || '_P is');
 pl('/*');
 pl('|| purpose: basic rutines for table ' || ip_table_name);
 pl('||');
 pl('|| created by: ' || ip_username);
 pl('*/');
 pl;
 pl('/*insert one record into Db, no commit*/');
 pl('procedure insert_'||lower(ip_table_name)||' ( ');

 pl_2tab('op_id out '  ||  ip_table_name || '.ID%TYPE');
 for  v_rec in v_cur_columns loop
         if v_rec.column_name <> 'ID' then
                  pl_2tab(',' ||make_input_param(ip_table_name,v_rec.column_name ));
         end if;
 end loop;
 pl_2tab(')  is');
 pl('begin');
 pl_1tab('op_id := db_utils_p.get_object_id();');
 pl_1tab('insert into ' || ip_table_name || ' (');
 v_separator := '';
 for v_rec in v_cur_columns loop
     pl_2tab(v_separator  || v_rec.column_name);
     v_separator := ',';
 end loop;
 pl_1tab(' )');
 pl_1tab('values (');
 v_separator := '';
 for v_rec in v_cur_columns loop
     if v_rec.column_name <> 'ID' then
       pl_2tab(v_separator  || 'ip_' || lower(v_rec.column_name) );
     else
       pl_2tab(v_separator  || 'op_' || lower(v_rec.column_name) );
     end if;
     v_separator := ',';
 end loop;
 pl_1tab(' );');

 pl('end ' || 'insert_'||lower(ip_table_name)||';');

  pl('/*');
 pl('|| delete using ID, no commit');
 pl('*/');
 pl('procedure delete_'||lower(ip_table_name)||' ( ');
 pl_2tab('ip_id ' || ip_table_name ||'.ID%TYPE');
 pl_2tab(') is');
 pl('begin');
 pl_1tab('delete from ' ||  ip_table_name ||' where ID = ip_id;');
 pl('end ' || 'delete_'||lower(ip_table_name)||';');

 pl('/*');
 pl('|| update entire record, no commit');
 pl('*/');
 pl('procedure update_'||lower(ip_table_name)||' ( ');
 v_separator := '';
 for  v_rec in v_cur_columns loop
         pl_2tab(v_separator ||make_input_param(ip_table_name,v_rec.column_name ));
         v_separator := ',';
 end loop;
 pl_2tab(') is');
  pl('begin');
  pl_1tab( 'update ' || ip_table_name);
  pl_1tab( ' set ');
  v_separator := '';
  for v_rec in v_cur_columns loop
     if v_rec.column_name <> 'ID' then
       pl_2tab(v_separator  || v_rec.column_name || ' = ' || 'ip_' || lower(v_rec.column_name) );
       v_separator := ',';
     end if;
  end loop;
  pl_1tab('where ID = ip_id;');
  pl('end ' || 'update_'||lower(ip_table_name) ||';');


 pl('/*END-OF-PACKAGE*/');
 pl('end ' || ip_table_name || '_P;');

end;
procedure gen_code_for_table( ip_table_name varchar2,
                              ip_username varchar2 := 'amazing') is
  v_table_name varchar2(30) := rtrim(ltrim( upper(ip_table_name)));
begin
     reset_data();
     commit;
     /*
     || sepeci
     */
     gen_speci(v_table_name,ip_username);
     pl('/');

     /*
     || body
     */
     gen_body(v_table_name,ip_username);
     pl('/');
     commit;
end gen_code_for_table;



end gen_table_package;
/
